from subsidy.app.mapper.BaseDB import BaseDb

class DbHelp(BaseDb):
    def __init__(self, logger=None):
        super().__init__()
        self.logger = logger
        print("数据库初始化完成")

    def __del__(self):
        self.connection.close()

    def isLogger(self, type, *data):
        if self.logger is None:
            print(data)
        else:
            if type == "info":
                self.logger.info(f"数据库Info信息: {data}")
            else:
                self.logger.debug(f"数据库Info信息: {data}")


    def saveVideoProgress(self, data_list: []):
        """课程数据和进度全部保存到数据库"""
        try:
            cursor = self.connection.cursor()
            # 构造插入语句，根据字典的键来对应列名
            columns = ', '.join(data_list[0].keys())
            placeholders = ', '.join(['%s'] * len(data_list[0]))
            sql = f"INSERT INTO progress_video ({columns}) VALUES ({placeholders})"
            # 提取字典中的值组成元组列表，用于executemany
            values_list = [tuple(d.values()) for d in data_list]
            cursor.executemany(sql, values_list)
            self.connection.commit()
            print("数据插入成功")
            self.isLogger("info", f"当前: Sql信息: {sql} 执行结果:数据插入成功, 共{len(data_list)}条数据")
        except Exception as e:
            print(e)
            self.isLogger("debug", f"当前: Sql信息: {sql} 执行失败: {e}")
        finally:
            # 关闭游标和连接
            cursor.close()


    def querAllClassInfo(self, idCar):
        """查询全部课程进度"""
        try:
            # 创建游标
            cursor = self.connection.cursor()
            sql = "SELECT * FROM progress_video where idCar = %s"
            cursor.execute(sql, (idCar,))
            results = cursor.fetchall()
            results = [result for result in results]
            self.isLogger("info", f"查询成功：{results}, 共{len(results)}条数据")
            return results
        except Exception as e:
            self.isLogger("debug", f"查询出错：{e}")
        finally:
            # 关闭游标
            cursor.close()

    def querAllProgressUndonInfo(self, idCar):
        """查询全部未完成课程进度"""
        try:
            # 创建游标
            cursor = self.connection.cursor()
            sql = "SELECT * FROM progress_video where idCar = %s and progress_time != total_time"
            cursor.execute(sql, (idCar,))
            results = cursor.fetchall()
            results = [result for result in results]
            self.isLogger("info", f"查询成功：{results}, 共{len(results)}条数据")
            return results
        except Exception as e:
            self.isLogger("debug", f"查询出错：{e}")
        finally:
            # 关闭游标
            cursor.close()

    def querOneVideoDeatils(self, idCar, vide_id):
        """查询特定的视频信息"""
        try:
            # 创建游标
            cursor = self.connection.cursor()
            sql = "SELECT * FROM progress_video where idCar = %s and video_id = %s"
            print("sql=========", sql)
            cursor.execute(sql, (idCar, vide_id))
            results = cursor.fetchone()
            self.isLogger("info", f"查询特定的视频信息成功：{results}, 共{len(results)}条数据")
            return results
        except Exception as e:
            self.isLogger("debug", f"查询出错：{e}")
        finally:
            # 关闭游标
            cursor.close()



    def update_Video_data(self, data_dict: dict):
        """更新视频进度"""
        try:
            cursor = self.connection.cursor()
            sql = f"UPDATE progress_video SET progress_time = %s WHERE idCar = %s AND video_id = %s"
            data = (data_dict['progress_time'], data_dict['idCar'], data_dict['video_id'])
            cursor.execute(sql, data)
            self.connection.commit()
            self.isLogger("info", f"用户 {data_dict['idCar']} 的信息已成功更新。")
        except Exception as e:
            print(f"更新用户信息时出错: {e}")
            # 回滚事务
            self.connection.rollback()

        finally:
            cursor.close()


    def delAllDbProgress(self, idCar):
        try:
            cursor = self.connection.cursor()
            sql = "DELETE FROM progress_video WHERE idCar = %s"
            data = (idCar,)
            cursor.execute(sql, data)
            self.connection.commit()
            self.isLogger("info", f"用户 {idCar} 的本地课程删除成功。")
        except Exception as e:
            print(f"更新用户信息时出错: {e}")
            # 回滚事务
            self.connection.rollback()
        finally:
            cursor.close()


    def update_sleep_time(self, data_dict:dict):
        """更新本地刷课时间"""
        try:
            cursor = self.connection.cursor()
            sql = f"UPDATE config SET time = %s WHERE idCar = %s AND video_id = %s"
            data = (data_dict['time'], data_dict['idCar'], data_dict['video_id'])
            cursor.execute(sql, data)
            self.connection.commit()
            self.isLogger("info", f"用户 {data_dict['idCar']} 的本地刷课时间成功更新。")
        except Exception as e:
            print(f"更新用户信息时出错: {e}")
            # 回滚事务
            self.connection.rollback()
        finally:
            cursor.close()




    def query_sleep_time(self, data_dict:dict):
        """查询刷课时间"""
        try:
            cursor = self.connection.cursor()
            sql = "SELECT * FROM config where idCar = %s and video_id = %s"
            data = (data_dict['idCar'], data_dict['video_id'])
            cursor.execute(sql, data)
            results = cursor.fetchall()
            return results
            self.isLogger("info", f"用户 {data_dict['idCar']} 的本地时间已成功查询{results}。")
        except Exception as e:
            print(f"更新用户信息时出错: {e}")
        finally:
            cursor.close()

    def set_sleep_time(self, data_dict:dict):
        """插入刷课时间"""
        try:
            print(data_dict)
            cursor = self.connection.cursor()
            sql = "INSERT INTO config (time, video_id, idCar) VALUES (%s, %s, %s)"
            data = (data_dict['time'], data_dict['video_id'], data_dict['idCar'])
            cursor.execute(sql, data)
            self.connection.commit()
            self.isLogger("info", f"用户 {data_dict['idCar']} 的本地时间已成功插入。")
        except Exception as e:
            print(f"插入用户信息时出错: {e}")
            self.connection.rollback()
        finally:
            cursor.close()

if __name__ == '__main__':
    import logging
    import os

    logger = logging.getLogger("flashClass")
    logger.setLevel(logging.DEBUG)
    file_handler = logging.FileHandler(os.path.basename(__file__).replace(".py", ".log"), encoding="utf-8")
    console_handler = logging.StreamHandler()
    logger.addHandler(file_handler)
    logger.addHandler(console_handler)
    db = DbHelp(logger)
    db.querAllProgressUndonInfo("360728200305202539")
